﻿using WebSocketServer.Models;
using JmShared;
using MySql.Data.MySqlClient;
using MySql.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using WebSocketServer.models;
using WebSocketServer.Utilities;
using System.Security;
using System.Security.Cryptography;

namespace WebSocketServer.Managers
{
    public class DatabaseManager
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger
(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private readonly SecureString StoredMessagePassPhrase;
        private readonly string ConnStr;
        public DatabaseManager(string server, string user, string database, string port, string password, string storedMessagePassPhrase)
        {
            this.ConnStr = String.Format("server={0};user={1};database={2};port={3};password={4}", server,
user, database, port, password);
            this.StoredMessagePassPhrase = StaticMethods.ToSecureString(storedMessagePassPhrase);

        }

        void OnInfoMessage(object sender, MySqlInfoMessageEventArgs args)
        {
            foreach (MySqlError error in args.errors)
            {
                log.Error(string.Format("MySQL error: {0} - {1} : {2}", error.Level, error.Code, error.Message));
            }
        }


        public DataTable ReadUserSettings(DeviceType type, int memberID, string deviceID, bool ignoreDeviceMatch = false)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;

                string queryString = string.Empty;
                switch (type)
                {
                    case DeviceType.PC:
                        queryString = @"SET SESSION group_concat_max_len = 1000000;
                                        SELECT SQL_NO_CACHE d.deviceid as 'deviceid',pc_us.*,us.*,a.nickname,
                                        CONCAT_WS('',GROUP_CONCAT(DISTINCT o.groups_id,'|',o.ispinned),':',GROUP_CONCAT(DISTINCT opm.email,'|',pm.ispinned)) as 'openchats',
                                        GROUP_CONCAT(DISTINCT ue.keyword,'|',(SELECT e.image IS NOT NULL AS 'exists'),'|',e.md5,'|',ue.orderindex,'|',e.extension) AS 'customemoticons',
                                        GROUP_CONCAT(DISTINCT i.email) AS 'ignoredcontacts',
                                        GROUP_CONCAT(DISTINCT bg.groups_id) AS 'blockedgroups',
                                        GROUP_CONCAT(DISTINCT av.md5,'|',av.extension) AS 'avatar'
                                        FROM usersettings us
                                        LEFT JOIN pc_usersettings pc_us ON us.members_memberid = pc_us.members_memberid AND us.devices_deviceid=pc_us.devices_deviceid
                                        LEFT JOIN accountsettings a ON a.members_memberid=pc_us.members_memberid
                                        LEFT JOIN avatars av ON av.md5=a.avatars_md5
                                        LEFT JOIN opengroupchats o ON o.members_memberid=pc_us.members_memberid AND o.devices_deviceid=pc_us.devices_deviceid
                                        LEFT JOIN openprivatechats pm ON pm.members_memberid=pc_us.members_memberid AND pm.devices_deviceid=pc_us.devices_deviceid
                                        LEFT JOIN members opm ON pm.members_receiverid = opm.memberid
                                        LEFT JOIN useremoticons ue ON ue.members_memberid = pc_us.members_memberid
                                        LEFT JOIN emoticons e ON e.md5 = ue.emoticons_md5
                                        LEFT JOIN members i ON i.memberid IN (SELECT members_friendid FROM ignoredcontacts WHERE members_memberid = pc_us.members_memberid)
                                        LEFT JOIN blockedgroups bg ON bg.members_memberid=pc_us.members_memberid
                                        LEFT JOIN devices d ON d.deviceid=@DeviceID";
                        break;
                    case DeviceType.Android:
                        queryString = @"SET SESSION group_concat_max_len = 1000000;
                                        SELECT SQL_NO_CACHE d.deviceid as 'deviceid',aus.*,us.*,a.nickname,
                                        GROUP_CONCAT(DISTINCT ue.keyword,'|',(SELECT e.image IS NOT NULL AS 'exists'),'|',e.md5,'|',ue.orderindex,'|',e.extension) AS 'customemoticons',
                                        GROUP_CONCAT(DISTINCT i.email) AS 'ignoredcontacts',
                                        GROUP_CONCAT(DISTINCT bg.groups_id) AS 'blockedgroups',
                                        GROUP_CONCAT(DISTINCT av.md5,'|',av.extension) AS 'avatar'
                                        FROM usersetting us
                                        LEFT JOIN android_usersettings aus ON us.members_memberid = aus.members_memberid AND us.devices_deviceid=aus.devices_deviceid
                                        LEFT JOIN accountsettings a ON a.members_memberid=aus.members_memberid
                                        LEFT JOIN avatars av ON av.md5=a.avatars_md5
                                        LEFT JOIN useremoticons ue ON ue.members_memberid = aus.members_memberid
                                        LEFT JOIN emoticons e ON e.md5 = ue.emoticons_md5
                                        LEFT JOIN members i ON i.memberid IN (SELECT members_friendid FROM ignoredcontacts WHERE members_memberid = aus.members_memberid)
                                        LEFT JOIN blockedgroups bg ON bg.members_memberid=aus.members_memberid
                                        LEFT JOIN devices d ON d.deviceid=@DeviceID";
                        break;
                    case DeviceType.WindowsPhone:
                        queryString = @"SET SESSION group_concat_max_len = 1000000;
                                        SELECT SQL_NO_CACHE d.deviceid as 'deviceid',wp_us.*,us.*,a.nickname,
                                        GROUP_CONCAT(DISTINCT ue.keyword,'|',(SELECT e.image IS NOT NULL AS 'exists'),'|',e.md5,'|',ue.orderindex,'|',e.extension) AS 'customemoticons',
                                        GROUP_CONCAT(DISTINCT i.email) AS 'ignoredcontacts',
                                        GROUP_CONCAT(DISTINCT bg.groups_id) AS 'blockedgroups',
                                        GROUP_CONCAT(DISTINCT av.md5,'|',av.extension) AS 'avatar'
                                        FROM usersettings us
                                        LEFT JOIN wp_usersettings wp_us ON us.members_memberid = wp_us.members_memberid AND us.devices_deviceid=wp_us.devices_deviceid
                                        LEFT JOIN accountsettings a ON a.members_memberid=wp_us.members_memberid
                                        LEFT JOIN avatars av ON av.md5=a.avatars_md5
                                        LEFT JOIN useremoticons ue ON ue.members_memberid = wp_us.members_memberid
                                        LEFT JOIN emoticons e ON e.md5 = ue.emoticons_md5
                                        LEFT JOIN members i ON i.memberid IN (SELECT members_friendid FROM ignoredcontacts WHERE members_memberid = wp_us.members_memberid)
                                        LEFT JOIN blockedgroups bg ON bg.members_memberid=wp_us.members_memberid
                                        LEFT JOIN devices d ON d.deviceid=@DeviceID";
                        break;
                }
                if (ignoreDeviceMatch)
                    queryString += " WHERE us.members_memberid=@MemberID";
                else
                    queryString += " WHERE us.members_memberid=@MemberID AND us.devices_deviceid=d.deviceid";

                MySqlCommand cmd = conn.CreateCommand();
                System.Data.DataTable table = new System.Data.DataTable();

                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Parameters.Add(new MySqlParameter("@DeviceID", deviceID));
                cmd.CommandText = queryString;
                MySqlDataAdapter deviceSettingsAdapter = new MySqlDataAdapter(cmd);
                deviceSettingsAdapter.Fill(table);

                return table;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }



        public void StoreUserSettings(Dictionary<PropertyInfo, object> updatedValues, DeviceType type, int memberID, string deviceID)
        {
            string tableName = string.Empty;
            switch (type)
            {
                case DeviceType.Android:
                    tableName = "android_usersettings";
                    break;
                case DeviceType.PC:
                    tableName = "pc_usersettings";
                    break;
                case DeviceType.WindowsPhone:
                    tableName = "wp_usersettings";
                    break;
            }

            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();
            MySqlCommand cmd = conn.CreateCommand();

            try
            {
                List<MySqlParameter> myParam = new List<MySqlParameter>();
                string deviceSettingsUpdatestring = string.Empty;
                string deviceSettingsValuestring = string.Empty;
                string deviceSettingsColumnstring = string.Empty;
                string baseSettingsUpdatestring = string.Empty;
                string baseSettingsValuestring = string.Empty;
                string baseSettingsColumnstring = string.Empty;
                string accountUpdatestring = string.Empty;
                string accountValuestring = string.Empty;
                string accountColumnstring = string.Empty;
                string avatarsColumnString = string.Empty;
                string avatarsValueString = string.Empty;
                string OpenGroupChatsValuestring = string.Empty;
                string OpenGroupChatsColumnstring = string.Empty;
                string OpenPrivateChatsValuestring = string.Empty;
                string OpenPrivateChatsColumnstring = string.Empty;
                string openGroupIDs = string.Empty;
                string openPrivateChatIDs = string.Empty;
                string userEmoticonsUpdatestring = string.Empty;
                string userEmoticonsValuestring = string.Empty;
                string userEmoticonsColumnstring = string.Empty;
                string emoticonValuestring = string.Empty;
                string emoticonColumnstring = string.Empty;
                string userEmoticonKeys = string.Empty;

                foreach (KeyValuePair<PropertyInfo, object> kvp in updatedValues)
                {
                    PropertyInfo prop = kvp.Key;

                    string propertyName = kvp.Key.Name;
                    var attributes = prop.GetCustomAttributes(false);
                    UserInfoAttributeType? userInfoAttributeType = null;
                    UserSettingsType? userSettingsType = null;
                    if (attributes.Length > 0)
                    {
                        var userInfoAttribute = (UserInfoAttribute)attributes.FirstOrDefault(x => x is UserInfoAttribute);
                        if (userInfoAttribute != null)
                            userInfoAttributeType = userInfoAttribute.PropertyType;

                        var userSettingAttribute = (UserSettingsTypeAttribute)attributes.FirstOrDefault(x => x is UserSettingsTypeAttribute);
                        if (userSettingAttribute != null)
                            userSettingsType = userSettingAttribute.Settingstype;
                    }
                    object insertValue = kvp.Value;
                    if (propertyName == "ManualClientStatus")
                    {
                        ClientStatus? status = kvp.Value as ClientStatus?;
                        if (status.HasValue)
                            insertValue = (int)status.Value;
                    }
                    else if (propertyName == "ExpandedGroups")
                    {
                        IEnumerable<string> groups = (List<string>)kvp.Value;
                        insertValue = string.Join(",", groups);
                    }

                    if (userInfoAttributeType.HasValue && (userInfoAttributeType.Value == UserInfoAttributeType.Nickname))
                    {
                        myParam.Add(new MySqlParameter("@" + propertyName.ToLower(), insertValue));
                        accountUpdatestring += string.Format("{0}={1},", propertyName.ToLower(), myParam.Last().ParameterName);
                        accountValuestring += myParam.Last().ParameterName + ",";
                        accountColumnstring += propertyName.ToLower() + ",";
                    }
                    else if (userInfoAttributeType.HasValue && userInfoAttributeType.Value == UserInfoAttributeType.Avatar)
                    {
                        Avatar avatar = (Avatar)insertValue;
                        if (!string.IsNullOrEmpty(avatar.Hash))
                        {
                            myParam.Add(new MySqlParameter("@" + propertyName.ToLower(), avatar.Hash));
                            avatarsColumnString += "md5";
                            avatarsValueString += string.Format("({0})", myParam.Last().ParameterName);
                        }
                        else
                            myParam.Add(new MySqlParameter("@" + propertyName.ToLower(), DBNull.Value));

                        accountUpdatestring += string.Format("{0}={1},", "avatars_md5", myParam.Last().ParameterName);
                        accountValuestring += myParam.Last().ParameterName + ",";
                        accountColumnstring += "avatars_md5" + ",";
                    }
                    else if (userInfoAttributeType.HasValue && (userInfoAttributeType.Value == UserInfoAttributeType.CustomEmoticons))
                    {
                        userEmoticonsColumnstring += "members_memberid,keyword,emoticons_md5";
                        emoticonColumnstring += "md5";
                        int i = 0;
                        foreach (ContactEmoticon ce in (HashSet<ContactEmoticon>)insertValue)
                        {
                            userEmoticonKeys += string.Format("'{0}',", ce.Keyword);
                            string paramName = "emoticonparam" + i;
                            myParam.Add(new MySqlParameter("@" + paramName, ce.Keyword));
                            myParam.Add(new MySqlParameter("@" + paramName + "hash", ce.Hash));

                            userEmoticonsValuestring += string.Format("(@MemberID,{0},{1}),", "@" + paramName, "@" + paramName + "hash");
                            emoticonValuestring += string.Format("({0}),", "@" + paramName + "hash");
                            i++;
                        }
                        userEmoticonsValuestring = userEmoticonsValuestring.TrimEnd(",");
                        emoticonValuestring = emoticonValuestring.TrimEnd(",");

                        userEmoticonsUpdatestring = "keyword=VALUES(keyword), emoticons_md5=VALUES(emoticons_md5)";

                        userEmoticonKeys = userEmoticonKeys.TrimEnd(",");
                        if (string.IsNullOrEmpty(userEmoticonKeys))
                            userEmoticonKeys += "''";
                    }
                    else if (propertyName == "OpenChats")
                    {
                        var openChats = (IList<OpenChat>)insertValue;

                        OpenGroupChatsColumnstring += "groups_id,members_memberid,devices_deviceid,ispinned";
                        OpenPrivateChatsColumnstring += "members_receiverid,members_memberid,devices_deviceid,ispinned";
                        int i = 0;
                        foreach (OpenChat chat in openChats)
                        {
                            if (chat.Type == ChatType.GroupChat)
                            {
                                myParam.Add(new MySqlParameter("@group" + i, chat.ID));
                                myParam.Add(new MySqlParameter("@chatispinned" + i, chat.IsPinned));
                                OpenGroupChatsValuestring += string.Format("({0},@MemberID,@DeviceID,{1}),", "@group" + i, "@chatispinned" + i);
                            }
                            else if (chat.Type == ChatType.PrivateMessage)
                            {
                                myParam.Add(new MySqlParameter("@chatispinned" + i, chat.IsPinned));
                                OpenPrivateChatsValuestring += string.Format("((SELECT memberid FROM members WHERE email='{0}'),@MemberID,@DeviceID,{1}),", chat.ID, "@chatispinned" + i);
                            }
                            i++;
                        }
                        OpenGroupChatsValuestring = OpenGroupChatsValuestring.TrimEnd(",");
                        OpenPrivateChatsValuestring = OpenPrivateChatsValuestring.TrimEnd(",");
                        foreach (OpenChat chat in openChats.Where(x => x.Type == ChatType.GroupChat))
                            openGroupIDs += string.Format("'{0}',", chat.ID);
                        openGroupIDs = openGroupIDs.TrimEnd(",");

                        if (string.IsNullOrEmpty(openGroupIDs))
                            openGroupIDs += "''";

                        foreach (OpenChat chat in openChats.Where(x => x.Type == ChatType.PrivateMessage))
                            openPrivateChatIDs += string.Format("'{0}',", chat.ID);
                        openPrivateChatIDs = openPrivateChatIDs.TrimEnd(",");
                        if (string.IsNullOrEmpty(openPrivateChatIDs))
                            openPrivateChatIDs += "''";
                    }
                    else if (propertyName == "IgnoredContacts" || propertyName == "BlockedGroups")
                    {
                        //We need to skip these, as they are handled elsewhere.
                    }
                    else
                    {
                        myParam.Add(new MySqlParameter("@" + propertyName.ToLower(), insertValue));

                        if (userSettingsType.HasValue && userSettingsType.Value == UserSettingsType.BaseSettings)
                        {
                            baseSettingsUpdatestring += string.Format("{0}={1},", propertyName.ToLower(), myParam.Last().ParameterName);
                            baseSettingsValuestring += myParam.Last().ParameterName + ",";
                            baseSettingsColumnstring += propertyName.ToLower() + ",";
                        }
                        else
                        {
                            deviceSettingsUpdatestring += string.Format("{0}={1},", propertyName.ToLower(), myParam.Last().ParameterName);
                            deviceSettingsValuestring += myParam.Last().ParameterName + ",";
                            deviceSettingsColumnstring += propertyName.ToLower() + ",";
                        }
                    }
                }

                myParam.Add(new MySqlParameter("@MemberID", memberID));
                myParam.Add(new MySqlParameter("@DeviceID", deviceID));
                // Start a local transaction

                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                if (!string.IsNullOrEmpty(deviceSettingsColumnstring))
                {
                    deviceSettingsColumnstring += "members_memberid,";
                    deviceSettingsValuestring += "@MemberID,";
                    deviceSettingsColumnstring += "devices_deviceid";
                    deviceSettingsValuestring += "@DeviceID";
                    deviceSettingsUpdatestring = deviceSettingsUpdatestring.TrimEnd(",");

                    baseSettingsColumnstring += "members_memberid,";
                    baseSettingsValuestring += "@MemberID,";
                    baseSettingsColumnstring += "devices_deviceid";
                    baseSettingsValuestring += "@DeviceID";
                    baseSettingsUpdatestring = baseSettingsUpdatestring.TrimEnd(",");

                    cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2}) ON DUPLICATE KEY UPDATE {3}", tableName, deviceSettingsColumnstring, deviceSettingsValuestring, deviceSettingsUpdatestring);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2}) ON DUPLICATE KEY UPDATE {3}", "usersettings", baseSettingsColumnstring, baseSettingsValuestring, baseSettingsUpdatestring);
                    cmd.ExecuteNonQuery();
                }
                if (!string.IsNullOrEmpty(avatarsColumnString) && !string.IsNullOrEmpty(avatarsValueString))
                {
                    cmd.CommandText = string.Format("INSERT IGNORE INTO avatars ({0}) VALUES {1}", avatarsColumnString, avatarsValueString);
                    cmd.ExecuteNonQuery();
                }
                if (!string.IsNullOrEmpty(accountColumnstring))
                {
                    accountColumnstring += "members_memberid";
                    accountValuestring += "@MemberID";
                    accountUpdatestring = accountUpdatestring.TrimEnd(",");

                    cmd.CommandText = string.Format("INSERT INTO accountsettings ({0}) VALUES ({1}) ON DUPLICATE KEY UPDATE {2}", accountColumnstring, accountValuestring, accountUpdatestring);
                    cmd.ExecuteNonQuery();
                }
                if (!string.IsNullOrEmpty(OpenGroupChatsColumnstring))
                {
                    cmd.CommandText = "DELETE FROM opengroupchats WHERE devices_deviceid=@DeviceID AND members_memberid=@MemberID";
                    int result = cmd.ExecuteNonQuery();
                    if (!string.IsNullOrEmpty(OpenGroupChatsValuestring))
                    {
                        cmd.CommandText = string.Format("INSERT IGNORE INTO opengroupchats ({0}) VALUES {1}", OpenGroupChatsColumnstring, OpenGroupChatsValuestring);
                        cmd.ExecuteNonQuery();
                    }
                }
                if (!string.IsNullOrEmpty(OpenPrivateChatsColumnstring))
                {
                    cmd.CommandText = "DELETE FROM openprivatechats WHERE devices_deviceid=@DeviceID AND members_memberid=@MemberID";
                    int result = cmd.ExecuteNonQuery();

                    if (!string.IsNullOrEmpty(OpenPrivateChatsValuestring))
                    {
                        cmd.CommandText = string.Format("INSERT IGNORE INTO openprivatechats ({0}) VALUES {1}", OpenPrivateChatsColumnstring, OpenPrivateChatsValuestring);
                        cmd.ExecuteNonQuery();
                    }
                }

                if (!string.IsNullOrEmpty(emoticonColumnstring) && !string.IsNullOrEmpty(emoticonValuestring))
                {
                    cmd.CommandText = string.Format("INSERT IGNORE INTO emoticons ({0}) VALUES {1}", emoticonColumnstring, emoticonValuestring);
                    cmd.ExecuteNonQuery();
                }
                if (!string.IsNullOrEmpty(userEmoticonsColumnstring))
                {
                    if (!string.IsNullOrEmpty(userEmoticonsValuestring))
                    {
                        cmd.CommandText = string.Format("INSERT INTO useremoticons ({0}) VALUES {1} ON DUPLICATE KEY UPDATE {2}", userEmoticonsColumnstring, userEmoticonsValuestring, userEmoticonsUpdatestring);
                        cmd.ExecuteNonQuery();

                    }

                    cmd.CommandText = string.Format("DELETE FROM useremoticons WHERE members_memberid=@MemberID AND keyword NOT IN ({0})", userEmoticonKeys);
                    cmd.ExecuteNonQuery();
                }
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }
                string lastCmd = "N/A";
                if (cmd != null)
                    lastCmd = cmd.CommandText;
                log.Error(string.Format("Error in {0}. Nothing was written. Last command was : {1} -- ", System.Reflection.MethodBase.GetCurrentMethod().Name, lastCmd), ex);
            }
            finally
            {
                conn.Close();

            }
        }


        public void ResetDeliveredMessageCount(string deviceID, int memberID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                List<MySqlParameter> myParam = new List<MySqlParameter>();
                myParam.Add(new MySqlParameter("@ReceivedMessageCount", (object)0));
                myParam.Add(new MySqlParameter("@DeliveredMessageCount", (object)0));
                myParam.Add(new MySqlParameter("@DeviceID", deviceID));
                myParam.Add(new MySqlParameter("@MemberId", memberID));
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"INSERT INTO pushmessageinfo (devices_deviceid, members_memberid, receivedmessagecount,deliveredmessagecount)
                                          VALUES(@DeviceID,@MemberID,@ReceivedMessageCount,@DeliveredMessageCount)
                                          ON DUPLICATE KEY
                                          UPDATE receivedmessagecount=@ReceivedMessageCount, deliveredmessagecount=@DeliveredMessageCount";
                cmd.ExecuteNonQuery();
                myTrans.Commit();

            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }

                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        public void StoreDeliveredMessageCount(List<OfflineClient> clients)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                // foreach (var client in clients)
                // {

                //string valueString = string.Empty;
                //foreach (var client in clients)
                //{
                //    valueString += string.Format("('{0}',{1},{2},{3}),", client.DeviceID, client.MemberID, client.ReceivedMessageCount, client.DeliveredMessageCount);
                //}
                //valueString = valueString.TrimEnd(',');


                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                // cmd.Parameters.AddRange(myParam.ToArray());
                //cmd.Parameters.Add(new MySqlParameter("@Values", valueString));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                //                cmd.CommandText = string.Format(@"INSERT INTO pushmessageinfo (devices_deviceid, members_memberid, receivedmessagecount,deliveredmessagecount)
                //                                          VALUES {0}
                //                                          ON DUPLICATE KEY
                //                                          UPDATE receivedmessagecount=VALUES(receivedmessagecount), deliveredmessagecount=VALUES(deliveredmessagecount)", valueString);
                cmd.CommandText = @"INSERT INTO pushmessageinfo (devices_deviceid, members_memberid, receivedmessagecount,deliveredmessagecount,lastsentpushtime)
                                          VALUES (@DeviceID,@MemberID,@ReceivedMessageCount,@DeliveredMessageCount,@LastSentPushTime)
                                          ON DUPLICATE KEY
                                          UPDATE receivedmessagecount=VALUES(receivedmessagecount), deliveredmessagecount=VALUES(deliveredmessagecount), lastsentpushtime=VALUES(lastsentpushtime)";
                cmd.Prepare();

                cmd.Parameters.Add(new MySqlParameter("@ReceivedMessageCount", 1));
                cmd.Parameters.Add(new MySqlParameter("@DeliveredMessageCount", 1));
                cmd.Parameters.Add(new MySqlParameter("@DeviceID", "1"));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", 1));
                cmd.Parameters.Add(new MySqlParameter("@LastSentPushTime", DateTime.UtcNow));

                foreach (var client in clients)
                {
                    //  valueString += string.Format("('{0}',{1},{2},{3}),", client.DeviceID, client.MemberID, client.ReceivedMessageCount, client.DeliveredMessageCount);
                    cmd.Parameters["@ReceivedMessageCount"].Value = client.ReceivedMessageCount;
                    cmd.Parameters["@DeliveredMessageCount"].Value = client.DeliveredMessageCount;
                    cmd.Parameters["@DeviceID"].Value = client.DeviceID;
                    cmd.Parameters["@MemberID"].Value = client.MemberID;
                    cmd.ExecuteNonQuery();
                }

                //}
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }
        public void StoreDeliveredMessageCount(ChatClient client)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {

                List<MySqlParameter> myParam = new List<MySqlParameter>();
                myParam.Add(new MySqlParameter("@ReceivedMessageCount", (object)client.ReceivedMessageCount));
                myParam.Add(new MySqlParameter("@DeliveredMessageCount", (object)client.DeliveredMessageCount));
                myParam.Add(new MySqlParameter("@DeviceID", client.DeviceID));
                myParam.Add(new MySqlParameter("@MemberID", client.MemberID));
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"INSERT INTO pushmessageinfo (devices_deviceid, members_memberid, receivedmessagecount,deliveredmessagecount)
                                          VALUES(@DeviceID,@MemberID,@ReceivedMessageCount,@DeliveredMessageCount)
                                          ON DUPLICATE KEY
                                          UPDATE receivedmessagecount=@ReceivedMessageCount, deliveredmessagecount=@DeliveredMessageCount";
                cmd.ExecuteNonQuery();

                myTrans.Commit();

            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }
        public List<UserInfo> ReadContactClients(int currentMemberID, string currentDeviceID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@MemberID", currentMemberID));
                cmd.CommandText = @"SELECT SQL_NO_CACHE fm.email,fm.memberid,d.deviceid,d.lastlogofftime,us.fontfamily,us.fontstyle,us.fontweight,us.fonthexcolor,a.nickname
                                  FROM members m
                                  INNER JOIN friends fr ON fr.members_memberid=m.memberid
                                  INNER JOIN members fm ON fm.memberid = fr.members_friendid 
                                  INNER JOIN devices d ON d.members_memberid = fm.memberid
                                  LEFT JOIN accountsettings a ON a.members_memberid=fm.memberid
							      LEFT JOIN usersettings us  ON us.members_memberid = fm.memberid AND us.devices_deviceid=d.deviceid
                                  WHERE m.memberid=@MemberID
                                  UNION DISTINCT
								  SELECT fm.email,fm.memberid,d.deviceid,d.lastlogofftime,us.fontfamily,us.fontstyle,us.fontweight,us.fonthexcolor,a.nickname
                                  FROM members m
                                  INNER JOIN friends fr ON fr.members_friendid=m.memberid
                                  INNER JOIN members fm ON fm.memberid=fr.members_memberid
                                  INNER JOIN devices d ON d.members_memberid = fm.memberid
                                  LEFT JOIN accountsettings a ON a.members_memberid=fm.memberid
								  LEFT JOIN usersettings us  ON us.members_memberid = fm.memberid AND us.devices_deviceid=d.deviceid
                                  WHERE m.memberid=@MemberID
								  UNION DISTINCT
								  SELECT m.email,m.memberid,d.deviceid,d.lastlogofftime,us.fontfamily,us.fontstyle,us.fontweight,us.fonthexcolor,a.nickname
                                  FROM members m
                                  INNER JOIN devices d ON d.members_memberid = m.memberid
                                  LEFT JOIN accountsettings a ON a.members_memberid=m.memberid
								  LEFT JOIN usersettings us  ON us.members_memberid = m.memberid AND us.devices_deviceid=d.deviceid
                                  WHERE m.memberid=@MemberID";

                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);
                List<UserInfo> users = new List<UserInfo>();
                foreach (DataRow row in table.Rows)
                {
                    string nickname = string.Empty;
                    string deviceid = string.Empty;
                    string email = string.Empty;
                    int memberID = -1;
                    string fontfamily = string.Empty;
                    string fontweight = string.Empty;
                    string fontstyle = string.Empty;
                    string fonthexcolor = string.Empty;
                    DateTimeOffset? lastlogofftime = null;

                    foreach (DataColumn col in table.Columns)
                    {
                        if (row[col] == DBNull.Value)
                            continue;
                        switch (col.ColumnName)
                        {
                            case "nickname":
                                nickname = row[col].ToString();
                                break;
                            case "deviceid":
                                deviceid = row[col].ToString();
                                break;
                            case "lastlogofftime":
                                lastlogofftime = DateTimeOffset.Parse(row[col].ToString());
                                break;
                            case "email":
                                email = row[col].ToString();
                                break;
                            case "memberid":
                                memberID = Convert.ToInt32(row[col]);
                                break;
                            case "fontfamily":
                                fontfamily = row[col].ToString();
                                break;
                            case "fontstyle":
                                fontstyle = row[col].ToString();
                                break;
                            case "fontweight":
                                fontweight = row[col].ToString();
                                break;
                            case "fonthexcolor":
                                fonthexcolor = row[col].ToString();
                                break;
                        }
                    }
                    if (memberID > -1 && !string.IsNullOrEmpty(deviceid) && (memberID != currentMemberID || deviceid != currentDeviceID))
                    {

                        //HashSet<ContactEmoticon> customEmoticons = null;

                        //List<ChatGroup> groups = ReadChatGroups(conn, memberID);
                        //if (readCustomEmoticons)
                        //    customEmoticons = ReadCustomEmoticons(memberID, conn);
                        // var avatar = ReadAvatar(email);
                        UserInfo ui = new UserInfo(email, deviceid)
                        {
                            // Avatar = avatar,
                            NickName = nickname,
                            ClientStatus = ClientStatus.Offline,
                            //ChatGroups = groups,
                            FontFamily = fontfamily,
                            FontStyle = fontstyle,
                            FontWeight = fontweight,
                            Color = fonthexcolor,
                            TimestampOffline = lastlogofftime,
                            //  CustomEmoticons = customEmoticons
                        };
                        users.Add(ui);
                    }
                }
                return users;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new List<UserInfo>();
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Reads the users custom emoticon info from the database.
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public HashSet<ContactEmoticon> ReadCustomEmoticons(string username)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            var emoticons = new HashSet<ContactEmoticon>();
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;


                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.CommandText = @"SELECT ue.keyword,e.md5,ue.orderindex,e.extension
                                    FROM members m
                                    INNER JOIN useremoticons ue ON ue.members_memberid=m.memberid
                                    INNER JOIN emoticons e ON ue.emoticons_md5=e.md5
                                    WHERE m.email=@Username
                                    AND e.image IS NOT NULL";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);

                foreach (DataRow row in table.Rows)
                {
                    emoticons.Add(new ContactEmoticon()
                    {
                        Keyword = row[0].ToString(),
                        Hash = row[1].ToString(),
                        Index = Convert.ToInt32(row[2]),
                        Extension = row[3].ToString()
                    });
                }
                emoticons = new HashSet<ContactEmoticon>(emoticons.OrderBy(x => x.Index));
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
            return emoticons;

        }

        public List<string> ReadMissingEmoticonKeywordsForUser(int memberID)
        {
            List<string> missingEmoticons = new List<string>();
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.CommandText = @"SELECT keyword FROM useremoticons ue
                                    INNER JOIN emoticons e ON e.md5 = ue.emoticons_md5
                                    WHERE ue.members_memberid=@MemberID AND e.image IS NULL";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);

                foreach (DataRow row in table.Rows)
                {
                    missingEmoticons.Add(row[0].ToString());
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
            return missingEmoticons;
        }
        public List<Friend> ReadFriendsList(int memberID)
        {
            List<Friend> friendsList = new List<Friend>();
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.CommandText = @"select email, isignored,isgroupcontact from
                                    (SELECT fm.email, EXISTS(SELECT 1 FROM ignoredcontacts WHERE members_memberid=fr.members_memberid AND members_friendid=fm.memberid) as 'isignored',false as'isgroupcontact' from members fm 
                                    INNER JOIN friends fr ON (fr.members_memberid=@MemberID AND fm.memberid = fr.members_friendid)
                                    UNION DISTINCT
                                    SELECT fm.email,EXISTS(SELECT 1 FROM ignoredcontacts WHERE members_memberid=fr.members_memberid AND members_friendid=fm.memberid) as 'isignored', false as'isgroupcontact' from members fm 
                                    INNER JOIN friends fr ON (fr.members_friendid=@MemberID AND fm.memberid= fr.members_memberid)
                                    UNION DISTINCT
                                    SELECT fm.email,EXISTS(SELECT 1 FROM ignoredcontacts WHERE members_memberid=@MemberID AND members_friendid=fm.memberid) as 'isignored', true as'isgroupcontact' from members fm 
                                    INNER JOIN membergroups mg ON mg.members_memberid=fm.memberid
                                    INNER JOIN membergroups ourmg ON ourmg.groups_id=mg.groups_id AND ourmg.members_memberid = @MemberID
                                    WHERE fm.memberid !=@MemberID)x
                                    GROUP BY email";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);

                foreach (DataRow row in table.Rows)
                {
                    string username = string.Empty;
                    bool isIgnored = false;
                    bool isGroupContact = false;
                    foreach (DataColumn col in table.Columns)
                    {
                        if (col.ColumnName == "email")
                            username = row[col].ToString();
                        else if (col.ColumnName == "isignored")
                            isIgnored = Convert.ToBoolean(row[col]);
                        else if (col.ColumnName == "isgroupcontact")
                            isGroupContact = Convert.ToBoolean(row[col]);
                    }
                    friendsList.Add(new Friend(username, isIgnored, isGroupContact));
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
            return friendsList;
        }
        internal ContactEmoticonTransfer ReadCustomEmoticon(string deviceID, string username, string keyword)
        {

            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.Parameters.Add(new MySqlParameter("@Keyword", keyword));
                cmd.CommandText = @"SELECT ue.keyword,e.md5, e.image, e.extension 
                                    FROM members m
                                    INNER JOIN useremoticons ue ON ue.members_memberid = m.memberid AND ue.keyword=@Keyword
                                    INNER JOIN emoticons e ON e.md5 = ue.emoticons_md5
                                    WHERE m.email=@Username";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);
                ContactEmoticonTransfer ce = new ContactEmoticonTransfer() { SenderID = string.Format("{0}#{1}", username, deviceID) };
                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn col in table.Columns)
                    {
                        if (col.ColumnName == "keyword")
                            ce.KeyWord = row[col].ToString();
                        else if (col.ColumnName == "image" && row[col] != DBNull.Value)
                            ce.EmoticonByteArray = (byte[])row[col];
                        else if (col.ColumnName == "extension")
                            ce.Extension = row[col].ToString();
                        else if (col.ColumnName == "md5")
                            ce.Hash = row[col].ToString();
                    }
                }
                return ce.EmoticonByteArray != null ? ce : null;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Reads the avatar including image data for a user. Returns null if avatar does not exist.
        /// </summary>
        /// <param name="deviceID"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        internal AvatarTransfer ReadAvatarTransfer(string deviceID, string username)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.CommandText = @"SELECT av.md5, av.avatar, av.extension 
                                    FROM members m
                                    INNER JOIN accountsettings a ON a.members_memberid = m.memberid
                                    INNER JOIN avatars av ON av.md5 = a.avatars_md5
                                    WHERE m.email=@Username";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);
                AvatarTransfer transfer = new AvatarTransfer() { SenderID = string.Format("{0}#{1}", username, deviceID) };
                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn col in table.Columns)
                    {
                        if (col.ColumnName == "avatar" && row[col] != DBNull.Value)
                            transfer.AvatarByteArray = (byte[])row[col];
                        else if (col.ColumnName == "extension")
                            transfer.Extension = row[col].ToString();
                        else if (col.ColumnName == "md5")
                            transfer.Hash = row[col].ToString();
                    }
                }
                return transfer.AvatarByteArray != null ? transfer : null;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        internal bool CheckIfAvatarExists(string hash)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@Hash", hash));
                cmd.CommandText = @"SELECT EXISTS(SELECT 1 FROM avatars WHERE md5=@Hash AND avatar IS NOT NULL)";
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result == 1;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Reads the users avatar info from the database.
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal Avatar ReadAvatar(string username)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.CommandText = @"SELECT av.md5, av.extension 
                                    FROM members m
                                    INNER JOIN accountsettings a ON a.members_memberid = m.memberid
                                    INNER JOIN avatars av ON av.md5 = a.avatars_md5
                                    WHERE m.email=@Username";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);
                Avatar avatar = new Avatar();
                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn col in table.Columns)
                    {
                        if (col.ColumnName == "extension")
                            avatar.Extension = row[col].ToString();
                        else if (col.ColumnName == "md5")
                            avatar.Hash = row[col].ToString();
                    }
                }
                return avatar;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<OfflineClient> ReadOfflineWPToastPushClientsForUser(string username)
        {
            var offlineClients = new List<OfflineClient>();
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@Username", username));

                cmd.CommandText = @"SELECT d.deviceid,p.receivedmessagecount,p.deliveredmessagecount,p.pushuri
                                    FROM members m
                                    INNER JOIN devices d ON d.members_memberid = m.memberid AND d.isloggedin=1 AND d.isconnected=0
                                    INNER JOIN wp_usersettings s ON s.devices_deviceid=d.deviceid AND s.members_memberid=m.memberid AND s.sendpmpushwhileoffline=1 
                                    INNER JOIN pushmessageinfo p ON p.devices_deviceid=d.deviceid AND p.members_memberid=d.members_memberid AND p.pushuri IS NOT NULL
                                    WHERE m.email = @Username";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);

                foreach (DataRow row in table.Rows)
                {
                    OfflineClient oc = new OfflineClient();
                    oc.Username = username;
                    foreach (DataColumn col in table.Columns)
                    {
                        if (col.ColumnName == "deviceid")
                            oc.DeviceID = row[col].ToString();
                        else if (col.ColumnName == "receivedmessagecount")
                            oc.ReceivedMessageCount = (int)row[col];
                        else if (col.ColumnName == "deliveredmessagecount")
                            oc.DeliveredMessageCount = (int)row[col];
                        else if (col.ColumnName == "pushuri")
                            oc.PushUri = row[col].ToString();
                    }
                    if (!string.IsNullOrEmpty(oc.DeviceID))
                        offlineClients.Add(oc);
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
            return offlineClients;
        }


        public string ReadWindowsPushUri(string deviceID, int memberID)
        {
            string pushUri = "";
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@DeviceID", deviceID));
                cmd.Parameters.Add(new MySqlParameter("@MemberId", memberID));
                cmd.CommandText = @"SELECT pushuri
                                    FROM pushmessageinfo
                                    WHERE devices_deviceid=@DeviceID
                                    AND members_memberid=@MemberID";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                object obj = cmd.ExecuteScalar();
                if (obj != null)
                    pushUri = (string)obj;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
            return pushUri;
        }

        public List<OfflineClient> ReadOfflineWPTilePushTargets()
        {
            var offlineClients = new List<OfflineClient>();
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();


                cmd.CommandText = @"SELECT d.deviceid,d.timezoneoffset,m.email,m.memberid,p.receivedmessagecount,p.deliveredmessagecount,p.pushuri
                                    FROM pushmessageinfo p
                                    INNER JOIN devices d ON d.members_memberid = p.members_memberid AND d.deviceid=p.devices_deviceid AND d.isconnected=0 AND d.isloggedin=1
                                    INNER JOIN members m  ON m.memberid = p.members_memberid
                                    INNER JOIN wp_usersettings s ON (s.devices_deviceid=p.devices_deviceid AND s.members_memberid=p.members_memberid) AND s.istileenabled=1
                                    WHERE p.pushuri IS NOT NULL AND p.receivedmessagecount>p.deliveredmessagecount";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);


                foreach (DataRow row in table.Rows)
                {
                    OfflineClient oc = new OfflineClient();
                    foreach (DataColumn col in table.Columns)
                    {
                        if (col.ColumnName == "deviceid")
                            oc.DeviceID = row[col].ToString();
                        else if (col.ColumnName == "timezoneoffset")
                            oc.TimeZoneOffset = (double)row[col];
                        else if (col.ColumnName == "memberid")
                            oc.MemberID = (int)row[col];
                        else if (col.ColumnName == "email")
                            oc.Username = row[col].ToString();
                        else if (col.ColumnName == "receivedmessagecount")
                            oc.ReceivedMessageCount = (int)row[col];
                        else if (col.ColumnName == "deliveredmessagecount")
                            oc.DeliveredMessageCount = (int)row[col];
                        else if (col.ColumnName == "pushuri")
                            oc.PushUri = row[col].ToString();
                    }
                    oc.UnreadMessageStats = ReadOfflineUserUnreadMessageStats(conn, oc.MemberID, oc.DeviceID);
                    if (!string.IsNullOrEmpty(oc.DeviceID))
                        offlineClients.Add(oc);
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
            return offlineClients;
        }

        private Dictionary<string, UnreadUserMessages> ReadOfflineUserUnreadMessageStats(MySqlConnection conn, int memberID, string deviceID)
        {
            var userUnreadMessages = new Dictionary<string, UnreadUserMessages>();
            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Parameters.Add(new MySqlParameter("@DeviceID", deviceID));

                cmd.CommandText = @"SELECT s.storedtime,m.email as 'senderusername',accs.nickname as 'sendernickname', s.storedtime>p.lastsentpushtime as 'isnew' 
                                    FROM storedprivatemessages s
                                    LEFT JOIN accountsettings accs ON accs.members_memberid = s.members_senderid
                                    INNER JOIN devices d ON  d.members_memberid=s.members_receiverid AND d.deviceid=@DeviceID
                                    INNER JOIN pushmessageinfo p ON p.members_memberid=d.members_memberid AND p.devices_deviceid=d.deviceid
                                    INNER JOIN members m ON m.memberid = s.members_senderid
                                    where s.members_receiverid=1 AND s.storedtime>d.lastmessagesynctime
                                    UNION
                                    SELECT s.storedtime,g.groupname as 'senderusername',g.groupname as 'sendernickname', s.storedtime>p.lastsentpushtime as 'isnew' 
                                    FROM storedgroupmessages s
                                    LEFT JOIN accountsettings accs ON accs.members_memberid = s.members_senderid
                                    INNER JOIN devices d ON  d.deviceid=@DeviceID
                                    INNER JOIN pushmessageinfo p ON p.members_memberid=d.members_memberid AND p.devices_deviceid=d.deviceid
                                    INNER JOIN membergroups mg ON mg.members_memberid = d.members_memberid AND mg.groups_id=s.groups_id
                                    INNER JOIN groups g on g.id = s.groups_id
                                    where s.storedtime>d.lastmessagesynctime";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                DataTable table = new DataTable();
                adapter.Fill(table);


                foreach (DataRow row in table.Rows)
                {
                    //UnreadUserMessages um = new UnreadUserMessages();
                    DateTime lastReceivedMessageTime = DateTime.MinValue;
                    string senderUsername = "";
                    string senderNickname = "";
                    foreach (DataColumn col in table.Columns)
                    {
                        if (col.ColumnName == "storedtime")
                            lastReceivedMessageTime = Convert.ToDateTime(row[col]);
                        else if (col.ColumnName == "senderusername")
                            senderUsername = (string)row[col];
                        else if (col.ColumnName == "sendernickname")
                            senderNickname = (string)row[col];
                    }
                    if (!string.IsNullOrEmpty(senderUsername))
                    {
                        if (!userUnreadMessages.ContainsKey(senderUsername))
                            userUnreadMessages.Add(senderUsername, new UnreadUserMessages()
                            {
                                SenderNickname = senderNickname,
                                SenderUsername = senderUsername,
                                LastReceivedMessageTime = lastReceivedMessageTime,
                                UnreadMessageCount = 1
                            });
                        else
                            userUnreadMessages[senderUsername].UnreadMessageCount++;
                    }
                }
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {

            }
            return userUnreadMessages;
        }


        public List<NotificationBase> ReadNotifications(int memberID)
        {
            List<NotificationBase> notifications = new List<NotificationBase>();

            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                List<GroupInviteNotification> groupInviteNotifications = ReadGroupInviteNotifications(conn, memberID);
                List<FriendInviteNotification> friendInviteNotifications = ReadFriendInviteNotifications(conn, memberID);
                List<ServerMessageNotification> serverMessageNotification = ReadServerMessageNotification(conn, memberID);
                notifications.AddRange(groupInviteNotifications);
                notifications.AddRange(friendInviteNotifications);
                notifications.AddRange(serverMessageNotification);
                return notifications;
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return notifications;
            }
            finally
            {
                conn.Close();
            }
        }
        private List<FriendInviteNotification> ReadFriendInviteNotifications(MySqlConnection conn, int memberID)
        {
            var notifications = new List<FriendInviteNotification>();
            MySqlCommand cmd = conn.CreateCommand();
            cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
            cmd.CommandText = @"SELECT frn.isread,frn.notificationtext,m.email
                                FROM friendinvite_notifications frn
                                INNER JOIN members m ON m.memberid = frn.members_senderid
                                WHERE members_receiverid = @MemberID";

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            System.Data.DataTable table = new System.Data.DataTable();
            adapter.Fill(table);

            foreach (DataRow row in table.Rows)
            {
                bool isRead = false;
                string notificationText = string.Empty;
                string senderUsername = string.Empty;
                foreach (DataColumn col in table.Columns)
                {
                    if (col.ColumnName == "email")
                        senderUsername = row[col].ToString();
                    else if (col.ColumnName == "isread")
                        isRead = bool.Parse(row[col].ToString());
                    else if (col.ColumnName == "notificationtext")
                        notificationText = row[col].ToString();
                }
                if (!string.IsNullOrEmpty(senderUsername))
                {
                    var gi = new FriendInviteNotification
                    {
                        IsRead = isRead,
                        NotificationText = notificationText,
                        SenderUsername = senderUsername
                    };
                    notifications.Add(gi);
                }
            }
            return notifications;
        }

        private List<GroupInviteNotification> ReadGroupInviteNotifications(MySqlConnection conn, int memberID)
        {
            var notifications = new List<GroupInviteNotification>();
            MySqlCommand cmd = conn.CreateCommand();
            cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
            cmd.CommandText = @"SELECT gin.isread,gin.notificationtext,m.email,g.groupname,g.id as 'groupid'
                                FROM groupinvite_notifications gin
                                INNER JOIN members m ON m.memberid = gin.members_senderid
								INNER JOIN groups g ON g.id = gin.groups_id
                                WHERE members_receiverid = @MemberID";

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            System.Data.DataTable table = new System.Data.DataTable();
            adapter.Fill(table);

            foreach (DataRow row in table.Rows)
            {
                //string receiverID = string.Empty;
                string senderUsername = string.Empty;
                string groupID = string.Empty;
                string groupName = string.Empty;
                bool isRead = false;
                string notificationText = string.Empty;

                foreach (DataColumn col in table.Columns)
                {
                    if (col.ColumnName == "email")
                        senderUsername = row[col].ToString();
                    else if (col.ColumnName == "groupid")
                        groupID = row[col].ToString();
                    else if (col.ColumnName == "groupname")
                        groupName = row[col].ToString();
                    else if (col.ColumnName == "isread")
                        isRead = bool.Parse(row[col].ToString());
                    else if (col.ColumnName == "notificationtext")
                        notificationText = row[col].ToString();
                }
                if (!string.IsNullOrEmpty(senderUsername))
                {
                    var gi = new GroupInviteNotification
                    {
                        Group = new ChatGroup(groupID, groupName),
                        IsRead = isRead,
                        NotificationText = notificationText,
                        SenderUsername = senderUsername
                    };
                    notifications.Add(gi);
                }
            }
            return notifications;
        }

        public List<ServerMessageNotification> ReadServerMessageNotification(MySqlConnection conn, int memberID)
        {
            var notifications = new List<ServerMessageNotification>();
            MySqlCommand cmd = conn.CreateCommand();
            cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));

            cmd.CommandText = @"SELECT sm.id, sm.text,smn.isread
                                FROM servermessage_notifications smn
                                INNER JOIN servermessages sm ON sm.id=smn.servermessages_id
                                WHERE members_receiverid = @MemberID";

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            System.Data.DataTable table = new System.Data.DataTable();
            adapter.Fill(table);

            foreach (DataRow row in table.Rows)
            {
                string id = string.Empty;
                bool isRead = false;
                string notificationText = string.Empty;

                foreach (DataColumn col in table.Columns)
                {
                    if (col.ColumnName == "id")
                        id = row[col].ToString();
                    else if (col.ColumnName == "isread")
                        isRead = bool.Parse(row[col].ToString());
                    else if (col.ColumnName == "text")
                        notificationText = row[col].ToString();
                }
                if (!string.IsNullOrEmpty(id))
                {
                    var notification = new ServerMessageNotification
                    {
                        ID = id,
                        IsRead = isRead,
                        NotificationText = notificationText,
                    };
                    notifications.Add(notification);
                }
            }
            return notifications;
        }

        /// <summary>
        /// Reads the chat groups the user is a member of from the database.
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public List<ChatGroup> ReadChatGroups(string username)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                return ReadChatGroups(conn, username);
            }
            catch (Exception ex)
            {
                log.Error(string.Format("Error in {0}", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
            return new List<ChatGroup>();
        }

        private Dictionary<string, GroupPrivilege> ReadGroupMembersForGroup(int groupID, MySqlConnection conn)
        {
            Dictionary<string, GroupPrivilege> groupMembers = new Dictionary<string, GroupPrivilege>();
            MySqlCommand cmd = conn.CreateCommand();

            cmd.Parameters.Clear();
            cmd.Parameters.Add(new MySqlParameter("@ID", groupID));
            cmd.CommandText = @"select m.email, mg.groupprivileges_id as 'privilege'
                                            FROM membergroups mg
                                            INNER JOIN members m ON m.memberid=mg.members_memberid
                                            WHERE mg.groups_id=@ID";
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            System.Data.DataTable table = new System.Data.DataTable();
            adapter.Fill(table);
            string username = string.Empty; ;
            GroupPrivilege privilege = GroupPrivilege.Member;
            foreach (DataRow row in table.Rows)
            {
                foreach (DataColumn col in table.Columns)
                {
                    if (col.ColumnName == "email")
                        username = row[col].ToString();
                    else if (col.ColumnName == "privilege")
                        privilege = (GroupPrivilege)Enum.Parse(typeof(GroupPrivilege), row[col].ToString());
                }
                if (!string.IsNullOrEmpty(username))
                    groupMembers.Add(username, privilege);
            }
            return groupMembers;
        }

        /// <summary>
        /// Reads a list of chat groups the user is member of using an existing MySqlConnection.
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="memberID"></param>
        /// <returns></returns>
        private List<ChatGroup> ReadChatGroups(MySqlConnection conn, string username)
        {
            List<ChatGroup> groups = new List<ChatGroup>();
            MySqlCommand cmd = conn.CreateCommand();
            cmd.Parameters.Add(new MySqlParameter("@Username", username));
            cmd.CommandText = @"SELECT g.id,g.groupname,mg.groupprivileges_id as 'privilege'
                                FROM members m
                                INNER JOIN membergroups mg ON mg.members_memberid=m.memberid
                                INNER JOIN groups g ON g.id=mg.groups_id
                                WHERE m.email=@Username";

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
            System.Data.DataTable table = new System.Data.DataTable();
            adapter.Fill(table);
            foreach (DataRow row in table.Rows)
            {
                string id = string.Empty;
                string name = string.Empty;
                Dictionary<string, GroupPrivilege> groupMembers = new Dictionary<string, GroupPrivilege>();
                foreach (DataColumn col in table.Columns)
                {
                    if (col.ColumnName == "id")
                    {
                        id = row[col].ToString();
                        groupMembers = ReadGroupMembersForGroup(Convert.ToInt32(id), conn);
                    }
                    else if (col.ColumnName == "groupname")
                        name = row[col].ToString();
                }
                if (!string.IsNullOrEmpty(id))
                {
                    ChatGroup g = new ChatGroup(id, name, groupMembers);
                    groups.Add(g);
                }
            }
            return groups;
        }


        /// <summary>
        /// Reads unread messages stored for the user
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public List<ChatMessage> ReadQueuedMessages(UserInfo user, int memberID, string deviceID, int messageCountLimit, int messageAgeLimit)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.InfoMessage += OnInfoMessage;
            var messages = new List<ChatMessage>();
            var privatemessages = ReadPrivateMessageQueue(conn, user.UserName, memberID, deviceID, messageCountLimit, messageAgeLimit);
            var groupMessages = ReadGroupMessageQueue(conn, user.ChatGroups, memberID, deviceID, messageCountLimit, messageAgeLimit);
            messages.AddRange(privatemessages);
            messages.AddRange(groupMessages);
            return messages;

        }
        /// <summary>
        /// Read queued private messages for a client.
        /// </summary>
        /// <param name="username">The UserID for the user requesting messages</param>
        /// <param name="deviceID">The device id for the device requesting messages</param>
        /// <param name="conn">The Sql connection to use</param>
        /// <param name="memberID">The MemberID of the client requesting the messages</param>
        /// <param name="messageCountLimit">Limit on how many messages should be returned</param>
        /// <param name="messageAgeLimit">Limit in hours on the age of a group message</param>
        /// <returns></returns>
        private List<ChatMessage> ReadPrivateMessageQueue(MySqlConnection conn, string username, int memberID, string deviceID, int messageCountLimit, int messageAgeLimit)
        {
            var messages = new List<ChatMessage>();
            MySqlCommand cmd = conn.CreateCommand();
            cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
            cmd.Parameters.Add(new MySqlParameter("@MessageCountLimit", messageCountLimit));
            cmd.Parameters.Add(new MySqlParameter("@MessageAgeLimit", messageAgeLimit));
            cmd.Parameters.Add(new MySqlParameter("@DeviceID", deviceID));

            cmd.CommandText = @"(SELECT SQL_NO_CACHE m.email,spm.devices_senderdeviceid,spm.message,spm.storedtime 
                                FROM storedprivatemessages spm
                                INNER JOIN members m ON m.memberid=spm.members_senderid
                                INNER JOIN devices d ON d.members_memberid=spm.members_receiverid AND d.deviceid=@DeviceID
                                WHERE spm.members_receiverid=@MemberID
                                AND spm.storedtime>d.lastmessagesynctime 
                                AND spm.storedtime > DATE_SUB(NOW(), INTERVAL @MessageAgeLimit HOUR)
                                ORDER BY storedtime DESC LIMIT @MessageCountLimit)
                                ORDER BY storedtime ASC";

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            System.Data.DataTable table = new System.Data.DataTable();
            adapter.Fill(table);

            foreach (DataRow row in table.Rows)
            {
                string senderDeviceID = string.Empty;
                string senderUsername = string.Empty;
                DateTime relayTime = DateTime.MinValue;
                string messageText = string.Empty;

                foreach (DataColumn col in table.Columns)
                {
                    if (col.ColumnName == "email")
                        senderUsername = row[col].ToString();
                    if (col.ColumnName == "devices_senderdeviceid")
                        senderDeviceID = row[col].ToString();
                    else if (col.ColumnName == "storedtime")
                        relayTime = DateTime.Parse(row[col].ToString());
                    else if (col.ColumnName == "message")
                        messageText = StringCipher.Decrypt(row[col].ToString(), StaticMethods.ToInsecureString(StoredMessagePassPhrase));
                }
                var msg = new ChatMessage(senderDeviceID, senderUsername, MessageType.PrivateMessage)
                {
                    Message = messageText,
                    RecipientID = username,
                    MessageRelayTime = relayTime,
                };
                DateTime.SpecifyKind(msg.MessageRelayTime.Value, DateTimeKind.Utc);
                messages.Add(msg);


            }
            return messages;
        }
        /// <summary>
        /// Read unread queued goup messages for a client.
        /// </summary>
        /// <param name="conn">The Sql connection to use</param>
        /// <param name="groups">List of groups to read message queue from</param>
        /// <param name="memberID">The MemberID of the user requesting the messages</param>
        /// <param name="deviceID">The device id for the device requesting messages</param>
        /// <param name="messageCountLimit">Limit on how many messages should be returned</param>
        /// <param name="messageAgeLimit">Limit in hours on the age of a group message</param>
        /// <returns></returns>
        private List<ChatMessage> ReadGroupMessageQueue(MySqlConnection conn, IEnumerable<ChatGroup> groups, int memberID, string deviceID, int messageCountLimit, int messageAgeLimit)
        {
            var messages = new List<ChatMessage>();
            MySqlCommand cmd = conn.CreateCommand();


            foreach (ChatGroup group in groups)
            {
                System.Data.DataTable table = new System.Data.DataTable();
                cmd.Parameters.Clear();
                cmd.Parameters.Add(new MySqlParameter("@GroupID", group.ID));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Parameters.Add(new MySqlParameter("@MessageCountLimit", messageCountLimit));
                cmd.Parameters.Add(new MySqlParameter("@MessageAgeLimit", messageAgeLimit));
                cmd.Parameters.Add(new MySqlParameter("@DeviceID", deviceID));
                //cmd.CommandText = "SELECT members_senderid,devices_senderdeviceid,message,storedtime FROM storedgroupmessages WHERE groups_id=@GroupID";
                cmd.CommandText = @"(SELECT SQL_NO_CACHE m.email,sgm.devices_senderdeviceid,sgm.message,sgm.storedtime 
                                    FROM storedgroupmessages sgm
                                    LEFT JOIN members m ON m.memberid=sgm.members_senderid
                                    LEFT JOIN devices d ON d.members_memberid=@MemberID AND d.deviceid=@DeviceID
                                    INNER JOIN membergroups mg ON mg.groups_id=@GroupID AND mg.members_memberid=@MemberID
                                    WHERE sgm.groups_id=@GroupID
                                    AND sgm.storedtime >d.lastmessagesynctime
                                    AND sgm.storedtime > DATE_SUB(NOW(), INTERVAL @MessageAgeLimit HOUR)
                                    ORDER BY storedtime DESC LIMIT @MessageCountLimit)
                                    ORDER BY storedtime ASC";
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

                adapter.Fill(table);

                foreach (DataRow row in table.Rows)
                {
                    string senderUsername = string.Empty;
                    string senderDeviceID = string.Empty;
                    DateTime relayTime = DateTime.MinValue;
                    string messageText = string.Empty;

                    foreach (DataColumn col in table.Columns)
                    {
                        if (col.ColumnName == "email")
                            senderUsername = row[col].ToString();
                        if (col.ColumnName == "devices_senderdeviceid")
                            senderDeviceID = row[col].ToString();
                        else if (col.ColumnName == "storedtime")
                            relayTime = DateTime.Parse(row[col].ToString());
                        else if (col.ColumnName == "message")
                            messageText = StringCipher.Decrypt(row[col].ToString(), StaticMethods.ToInsecureString(StoredMessagePassPhrase));
                    }

                    var msg = new ChatMessage(senderDeviceID, senderUsername, MessageType.GroupMessage)
                    {
                        Message = messageText,
                        RecipientID = group.ID,
                        MessageRelayTime = relayTime,
                    };
                    DateTime.SpecifyKind(msg.MessageRelayTime.Value, DateTimeKind.Utc);
                    messages.Add(msg);
                }
            }
            return messages;
        }

        /// <summary>
        /// Reads the selected number of the most recent group messages for the group.
        /// </summary>
        /// <param name="groupID">The groups ID</param>
        /// <param name="memberID">The MemberID of the user requesting the messages</param>
        /// <param name="messageCountLimit">Limit on how many messages should be returned</param>
        /// <param name="messageAgeLimit">Limit in hours on the age of a group message</param>
        /// <returns></returns>
        public List<ChatMessage> ReadGroupMessageQueue(string groupID, int memberID, int messageCountLimit, int messageAgeLimit)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            var messages = new List<ChatMessage>();
            MySqlCommand cmd = conn.CreateCommand();
            conn.InfoMessage += OnInfoMessage;

            System.Data.DataTable table = new System.Data.DataTable();
            cmd.Parameters.Clear();
            cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
            cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
            cmd.Parameters.Add(new MySqlParameter("@MessageCountLimit", messageCountLimit));
            cmd.Parameters.Add(new MySqlParameter("@MessageAgeLimit", messageAgeLimit));
            //cmd.CommandText = "SELECT members_senderid,devices_senderdeviceid,message,storedtime FROM storedgroupmessages WHERE groups_id=@GroupID";
            cmd.CommandText = @"(SELECT SQL_NO_CACHE m.email,sgm.devices_senderdeviceid,sgm.message,sgm.storedtime 
                                    FROM storedgroupmessages sgm
                                    LEFT JOIN members m ON m.memberid=sgm.members_senderid
                                    INNER JOIN membergroups mg ON mg.groups_id=@GroupID AND mg.members_memberid=@MemberID
                                    WHERE sgm.groups_id=@GroupID
                                    AND sgm.storedtime > DATE_SUB(NOW(), INTERVAL @MessageAgeLimit HOUR)
                                    ORDER BY storedtime DESC LIMIT @MessageCountLimit)
                                    ORDER BY storedtime ASC";
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            adapter.Fill(table);

            foreach (DataRow row in table.Rows)
            {
                string senderUsername = string.Empty;
                string senderDeviceID = string.Empty;
                DateTime relayTime = DateTime.MinValue;
                string messageText = string.Empty;

                foreach (DataColumn col in table.Columns)
                {
                    if (col.ColumnName == "email")
                        senderUsername = row[col].ToString();
                    if (col.ColumnName == "devices_senderdeviceid")
                        senderDeviceID = row[col].ToString();
                    else if (col.ColumnName == "storedtime")
                        relayTime = DateTime.Parse(row[col].ToString());
                    else if (col.ColumnName == "message")
                        messageText = StringCipher.Decrypt(row[col].ToString(), StaticMethods.ToInsecureString(StoredMessagePassPhrase));
                }
                var msg = new ChatMessage(senderDeviceID, senderUsername, MessageType.GroupMessage)
                {
                    Message = messageText,
                    RecipientID = groupID,
                    MessageRelayTime = relayTime,
                };
                DateTime.SpecifyKind(msg.MessageRelayTime.Value, DateTimeKind.Utc);
                messages.Add(msg);
            }

            return messages;
        }
        /// <summary>
        /// Reads the selected number of the most recent private messages for the user.
        /// </summary>
        /// <param name="senderUsername">The username of the sender of the private messages</param>
        /// <param name="userID">The receiving client's user id</param>
        /// <param name="memberID">The MemberID of the receiving client</param>
        /// <param name="messageCountLimit">Limit on how many messages should be returned</param>
        /// <param name="messageAgeLimit">Limit in hours on the age of a group message</param>
        /// <returns></returns>
        public List<ChatMessage> ReadPrivateMessageQueue(string senderUsername, string userID, int memberID, int messageCountLimit, int messageAgeLimit)
        {
            var messages = new List<ChatMessage>();
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.InfoMessage += OnInfoMessage;
            MySqlCommand cmd = conn.CreateCommand();
            cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
            cmd.Parameters.Add(new MySqlParameter("@MessageCountLimit", messageCountLimit));
            cmd.Parameters.Add(new MySqlParameter("@MessageAgeLimit", messageAgeLimit));
            cmd.Parameters.Add(new MySqlParameter("@SenderUsername", senderUsername));

            cmd.CommandText = @"(SELECT SQL_NO_CACHE spm.devices_senderdeviceid,sender_m.email as 'sender_email',receiver_m.email as 'receiver_email',spm.message,spm.storedtime 
                                FROM storedprivatemessages spm
                                INNER JOIN members receiver_m ON receiver_m.memberid=spm.members_receiverid
                                INNER JOIN members sender_m ON sender_m.memberid = spm.members_senderid AND sender_m.email=@SenderUsername
                                WHERE spm.members_receiverid=@MemberID AND spm.members_senderid=sender_m.memberid
                                AND spm.storedtime > DATE_SUB(NOW(), INTERVAL @MessageAgeLimit HOUR)
                                ORDER BY storedtime DESC LIMIT @MessageCountLimit)
                                UNION
                                (SELECT spm.devices_senderdeviceid,sender_m.email as 'sender_email',receiver_m.email as 'receiver_email',spm.message,spm.storedtime 
                                FROM storedprivatemessages spm
                                INNER JOIN members m ON m.email=@SenderUsername
                                INNER JOIN members receiver_m ON receiver_m.memberid=spm.members_receiverid AND receiver_m.email=@SenderUsername
                                INNER JOIN members sender_m ON sender_m.memberid = spm.members_senderid
                                WHERE spm.members_senderid=@MemberID AND spm.members_receiverid=receiver_m.memberid
                                AND spm.storedtime > DATE_SUB(NOW(), INTERVAL @MessageAgeLimit HOUR)
                                ORDER BY storedtime DESC LIMIT @MessageCountLimit)
                                ORDER BY storedtime ASC";

            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);

            System.Data.DataTable table = new System.Data.DataTable();
            adapter.Fill(table);
            int i = 0;
            int numToSkip = table.Rows.Count - messageCountLimit;
            foreach (DataRow row in table.Rows)
            {
                i++;
                if (numToSkip > 0 && i <= numToSkip)
                    continue;
                string senderDeviceID = string.Empty;
                string receiverUsername = string.Empty;
                DateTime relayTime = DateTime.MinValue;
                string messageText = string.Empty;

                foreach (DataColumn col in table.Columns)
                {
                    if (col.ColumnName == "sender_email")
                        senderUsername = row[col].ToString();
                    else if (col.ColumnName == "receiver_email")
                        receiverUsername = row[col].ToString();
                    else if (col.ColumnName == "devices_senderdeviceid")
                        senderDeviceID = row[col].ToString();
                    else if (col.ColumnName == "storedtime")
                        relayTime = DateTime.Parse(row[col].ToString());
                    else if (col.ColumnName == "message")
                        messageText = StringCipher.Decrypt(row[col].ToString(), StaticMethods.ToInsecureString(StoredMessagePassPhrase));
                }

                var msg = new ChatMessage(senderDeviceID, senderUsername, MessageType.PrivateMessage)
                {
                    Message = messageText,
                    RecipientID = receiverUsername,
                    MessageRelayTime = relayTime,
                };
                DateTime.SpecifyKind(msg.MessageRelayTime.Value, DateTimeKind.Utc);
                messages.Add(msg);
            }
            return messages;
        }


        public void StorePushUri(string deviceID, int memberID, string pushuri)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {

                List<MySqlParameter> myParam = new List<MySqlParameter>();
                myParam.Add(new MySqlParameter("@DeviceID", deviceID));
                myParam.Add(new MySqlParameter("@MemberID", memberID));
                myParam.Add(new MySqlParameter("@PushURI", pushuri));

                MySqlCommand cmd = conn.CreateCommand();
                cmd.Prepare();
                // Start a local transaction
                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = "UPDATE pushmessageinfo SET pushuri=@PushURI WHERE devices_deviceid=@DeviceID AND members_memberid=@MemberID";
                cmd.ExecuteNonQuery();

                myTrans.Commit();

            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                //  log.Error(error);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Updates the user devices online,logged in and synced message time. If lastSendtMessageTime is null, it's not updated.
        /// </summary>
        /// <param name="user"></param>
        public void StoreDeviceSync(string deviceID, DeviceType type, int memberID, bool? isLoggedIn, DateTime? lastSendtMessageTime = null, bool? isConnected = null, double? timeZoneOffset = null)
        {
            if (!isLoggedIn.HasValue && !lastSendtMessageTime.HasValue && !isConnected.HasValue && !timeZoneOffset.HasValue)
                return;
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@DeviceID", deviceID));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));


                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                string columns = "deviceid, members_memberid";
                string values = "@DeviceID,@MemberID";
                string updateValues = "";
                if (lastSendtMessageTime.HasValue)
                {
                    cmd.Parameters.Add(new MySqlParameter("@StoredTime", lastSendtMessageTime.Value));
                    columns += ",lastmessagesynctime";
                    values += ",@Storedtime";
                    updateValues += "lastmessagesynctime=@Storedtime,";
                }
                if (isLoggedIn.HasValue)
                {
                    cmd.Parameters.Add(new MySqlParameter("@IsLoggedIn", isLoggedIn.Value));
                    columns += ",isloggedin";
                    values += ",@IsLoggedIn";
                    updateValues += "isloggedin=@IsLoggedIn,";
                }
                if (isConnected.HasValue)
                {
                    cmd.Parameters.Add(new MySqlParameter("@IsConnected", isConnected.Value));
                    cmd.Parameters.Add(new MySqlParameter("@LastLoggedOffTime", DateTime.UtcNow));
                    columns += ",lastlogofftime,isconnected";
                    values += ",@LastLoggedOffTime,@IsConnected";
                    updateValues += "lastlogofftime=@LastLoggedOffTime, isconnected=@IsConnected,";
                }
                if (timeZoneOffset.HasValue)
                {
                    cmd.Parameters.Add(new MySqlParameter("@TimeZoneOffset", timeZoneOffset.Value));
                    columns += ",timezoneoffset";
                    values += ",@TimeZoneOffset";
                    updateValues += "timezoneoffset=@TimeZoneOffset";
                }
                updateValues = updateValues.TrimEnd(",");

                string insertText = string.Format("INSERT {0} INTO devices ({1})", string.IsNullOrEmpty(updateValues) ? "IGNORE" : "", columns);
                string valueText = string.Format(" VALUES({0})", values);
                string updateText = !string.IsNullOrEmpty(updateValues) ? string.Format(" ON DUPLICATE KEY UPDATE {0}", updateValues) : "";

                cmd.CommandText = insertText + valueText + updateText;
                cmd.ExecuteNonQuery();

                myTrans.Commit();

            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        public void StorePrivateMessage(ChatMessage msg)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();

            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                List<MySqlParameter> myParam = new List<MySqlParameter>();
                myParam.Add(new MySqlParameter("@SenderUsername", msg.SenderUsername));
                myParam.Add(new MySqlParameter("@SenderDeviceID", msg.SenderDeviceID));
                myParam.Add(new MySqlParameter("@RecipientUsername", msg.RecipientID));
                myParam.Add(new MySqlParameter("@Message", StringCipher.Encrypt(msg.Message, StaticMethods.ToInsecureString(this.StoredMessagePassPhrase))));
                myParam.Add(new MySqlParameter("@Storedtime", msg.MessageRelayTime));
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"INSERT INTO storedprivatemessages (members_senderid,devices_senderdeviceid ,members_receiverid, message,storedtime)
                                    SELECT senderM.memberid,@SenderDeviceID,receiverM.memberid,@Message,@Storedtime
                                    From members senderM, members receiverM
                                    WHERE senderM.email=@SenderUsername
                                    AND receiverM.email=@RecipientUsername";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"UPDATE pushmessageinfo pm 
                                    SET pm.receivedmessagecount=pm.receivedmessagecount + 1 
                                    WHERE pm.members_memberid=(SELECT memberid FROM members WHERE email=@RecipientUsername)";
                cmd.ExecuteNonQuery();
                myTrans.Commit();

            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                //  log.Error(error);
            }
            finally
            {
                conn.Close();
            }
        }

        public void StoreGroupMessage(ChatMessage msg)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {

                List<MySqlParameter> myParam = new List<MySqlParameter>();
                myParam.Add(new MySqlParameter("@SenderUsername", msg.SenderUsername));
                myParam.Add(new MySqlParameter("@SenderDeviceID", msg.SenderDeviceID));
                myParam.Add(new MySqlParameter("@GroupID", msg.RecipientID));
                myParam.Add(new MySqlParameter("@Message", StringCipher.Encrypt(msg.Message, StaticMethods.ToInsecureString(this.StoredMessagePassPhrase))));
                myParam.Add(new MySqlParameter("@Storedtime", msg.MessageRelayTime));
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"INSERT INTO storedgroupmessages (groups_id,devices_senderdeviceid, members_senderid, message,storedtime)
                                          SELECT @GroupID,@SenderDeviceID,senderM.memberid,@Message,@Storedtime
                                          FROM members senderM WHERE senderM.email=@SenderUsername";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "UPDATE pushmessageinfo pm, membergroups mg SET pm.receivedmessagecount=pm.receivedmessagecount + 1 WHERE pm.members_memberid=mg.members_memberid AND mg.groups_id=@GroupID";
                cmd.ExecuteNonQuery();

                myTrans.Commit();

            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                // log.Error(error);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Removes expires group messages (over 48 hours old) from the database.
        /// </summary>
        public void CleanupExpiredGroupMessages()
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"DELETE FROM storedgroupmessages WHERE storedtime < NOW() - INTERVAL 48 HOUR";
                int rowsDeletedNum = cmd.ExecuteNonQuery();
                log.Info(string.Format("Deleted {0} expired group messages", rowsDeletedNum));

                cmd.CommandText = @"UPDATE pushmessageinfo p
                                    LEFT JOIN devices d ON d.members_memberid = p.members_memberid AND d.lastmessagesynctime > NOW() + INTERVAL 48 HOUR
                                    SET p.receivedmessagecount=(select count(*) FROM(
                                    select sgm.* from storedgroupmessages sgm
                                    left join devices d ON d.lastmessagesynctime < NOW() + INTERVAL 48 HOUR
                                    left join membergroups mg ON mg.members_memberid=d.members_memberid
                                    WHERE sgm.groups_id = mg.groups_id
                                    UNION
                                    select spm.* from storedprivatemessages spm
                                    left join devices d ON d.lastmessagesynctime < NOW() + INTERVAL 48 HOUR
                                    WHERE spm.members_receiverid = d.members_memberid)total)";

                int updatedPushmessageInfosCount = cmd.ExecuteNonQuery();
                log.Info(string.Format("Updated {0} received message counts in PushMessageInfo", updatedPushmessageInfosCount));
                myTrans.Commit();

            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                // log.Error(error);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Check if the username and password is valid. DatabaseQueryResult Value is the users MemberID as int.
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public DatabaseQueryResult LoginCheck(string username, string password)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            try
            {
                conn.Open();
                conn.InfoMessage += OnInfoMessage;
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.Parameters.Add(new MySqlParameter("@Password", password));
                cmd.CommandText = "SELECT memberid,password FROM members WHERE email=@Username AND activation is NULL";


                System.Data.DataTable table = new System.Data.DataTable();
                MySqlDataAdapter deviceSettingsAdapter = new MySqlDataAdapter(cmd);
                deviceSettingsAdapter.Fill(table);


                foreach (DataRow row in table.Rows)
                {
                    bool success = false;
                    int memberID = -1;
                    foreach (DataColumn col in table.Columns)
                    {
                        if (col.ColumnName == "password")
                            success = PasswordHash.ValidatePassword(password, row[col].ToString());
                        else if (col.ColumnName == "memberid")
                            memberID = Convert.ToInt32(row[col]);
                    }
                    if (success)
                        return new DatabaseQueryResult(success, string.Empty, memberID);
                }

                //if (table.Rows.Count == 0)
                return new DatabaseQueryResult(false, "Wrong username or password", -1);
                //else
                //{
                //    return new DatabaseQueryResult(true, string.Empty, Convert.ToInt32(table.Rows[0][0]));
                //}
            }
            catch (Exception ex)
            {
                log.Error("Error verifying login", ex);
            }
            finally
            {
                conn.Close();
            }
            return new DatabaseQueryResult(false, "An error occured in the login process", -1);
        }

        /// <summary>
        /// Create a chat group.  DatabaseQueryResult Value is the GroupID as int.
        /// </summary>
        /// <param name="groupName"></param>
        /// <param name="memberID"></param>
        /// <returns></returns>
        public DatabaseQueryResult CreateChatGroup(string groupName, int memberID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@Groupname", groupName));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = "Select Count(*) FROM groups WHERE members_ownerid=@MemberID AND groupname=@Groupname";
                int rowsAffected = Convert.ToInt32(cmd.ExecuteScalar());
                if (rowsAffected == 0)
                {
                    cmd.CommandText = @"INSERT INTO groups (groupname,members_ownerid) 
                                        VALUES (@Groupname,@MemberID);SELECT last_insert_id();";
                    int groupID = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                    cmd.CommandText = @"INSERT INTO membergroups (members_memberid,groups_id,groupprivileges_id) 
                                        VALUES (@MemberID,@GroupID,(SELECT id FROM groupprivileges WHERE privilege='owner'))";
                    cmd.ExecuteNonQuery();
                    myTrans.Commit();
                    return new DatabaseQueryResult(true, "Group created", groupID);
                }
                else
                    return new DatabaseQueryResult(false, "You already have a group with that name");

            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);

                return new DatabaseQueryResult(false, "Group creation failed");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Deleted a chat group belonging to the member. DatabaseQueryResult Value is null.
        /// </summary>
        /// <param name="groupID"></param>
        /// <param name="memberID"></param>
        /// <returns></returns>
        public DatabaseQueryResult DeleteChatGroup(string groupID, int memberID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"DELETE FROM groups WHERE id=@GroupID AND members_ownerid=@MemberID";
                int result = cmd.ExecuteNonQuery();
                myTrans.Commit();
                if (result == 0)
                    return new DatabaseQueryResult(false, "Could not delete group");
                else
                    return new DatabaseQueryResult(true, "Group Deleted");
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);

                return new DatabaseQueryResult(false, "An error occured while trying to delete a chat group");
            }
            finally
            {
                conn.Close();
            }
        }

        public void StoreNotificationReadStatusForUser(int memberID, IEnumerable<NotificationBase> notifications)
        {
            if (notifications == null)
                return;
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                foreach (NotificationBase notification in notifications)
                {
                    if (notification.NotificationType == NotificationType.FriendInvite)
                    {
                        FriendInviteNotification fn = (FriendInviteNotification)notification;
                        MySqlCommand cmd = conn.CreateCommand();
                        cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                        cmd.Parameters.Add(new MySqlParameter("@SenderUsername", fn.SenderUsername));
                        cmd.Parameters.Add(new MySqlParameter("@IsRead", fn.IsRead));
                        cmd.Connection = conn;
                        cmd.Transaction = myTrans;
                        cmd.CommandText = "UPDATE friendinvite_notifications SET isread=@IsRead WHERE members_receiverid=@MemberID AND members_senderid=(SELECT memberid FROM members WHERE email=@SenderUsername)";
                        cmd.ExecuteNonQuery();
                    }
                    else if (notification.NotificationType == NotificationType.GroupInvite)
                    {
                        var fn = (GroupInviteNotification)notification;

                        MySqlParameter[] myParam = new MySqlParameter[3];
                        myParam[0] = new MySqlParameter("@MemberID", memberID);
                        myParam[1] = new MySqlParameter("@GroupID", fn.Group);
                        myParam[2] = new MySqlParameter("@IsRead", fn.IsRead);

                        MySqlCommand cmd = conn.CreateCommand();
                        cmd.Parameters.AddRange(myParam);
                        cmd.Connection = conn;
                        cmd.Transaction = myTrans;
                        cmd.CommandText = "UPDATE groupinvite_notifications SET isread=@IsRead WHERE groups_id=@GroupID AND members_receiverid=@MemberID";
                        cmd.ExecuteNonQuery();
                    }
                }
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Stores a group chat invite. DatabaseQueryResult Value is null.
        /// </summary>
        /// <param name="senderMemberID"></param>
        /// <param name="recipientUsername"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public DatabaseQueryResult StoreGroupChatInvite(int senderMemberID, string recipientUsername, string groupID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                List<MySqlParameter> myParam = new List<MySqlParameter>();
                myParam.Add(new MySqlParameter("@GroupID", groupID));
                myParam.Add(new MySqlParameter("@RecipientUsername", recipientUsername));
                myParam.Add(new MySqlParameter("@SenderMemberID", senderMemberID));
                myParam.Add(new MySqlParameter("@IsRead", false));
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"INSERT IGNORE INTO groupinvite_notifications (members_senderid,members_receiverid,groups_id,isread)
                                          SELECT @SenderMemberID,receiverM.memberid,@GroupID,@IsRead from members receiverM
                                          WHERE receiverM.email=@RecipientUsername";
                int result = cmd.ExecuteNonQuery();

                myTrans.Commit();
                string statusText = result > 0 ? "Group invite sent" : "This member already have a group invitation pending";

                return new DatabaseQueryResult(result > 0, statusText);
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "Group invite failed with an exception");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Store a friend request. DatabaseQueryResult Value is null.
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="friendUsername"></param>
        /// <returns></returns>
        public DatabaseQueryResult StoreFriendRequest(int memberID, string friendUsername, string messageToReceiver)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                List<MySqlParameter> myParam = new List<MySqlParameter>();
                myParam.Add(new MySqlParameter("@MemberID", memberID));
                myParam.Add(new MySqlParameter("@RecipientUsername", friendUsername));
                myParam.Add(new MySqlParameter("@IsRead", false));
                myParam.Add(new MySqlParameter("@NotificationText", messageToReceiver));
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"SELECT COUNT(*) FROM members WHERE email=@RecipientUsername";
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                if (result == 0)
                    return new DatabaseQueryResult(false, "No member with that email is registered");

                cmd.CommandText = @"SELECT COUNT(*)
                                    FROM friends
                                    WHERE (members_memberid = @MemberID
                                    AND members_friendid= ( SELECT memberid FROM members WHERE email=@RecipientUsername))
                                    OR (members_memberid = ( SELECT memberid FROM members WHERE email=@RecipientUsername)
                                    AND members_friendid= @MemberID)";
                result = Convert.ToInt32(cmd.ExecuteScalar());
                if (result > 0)
                    return new DatabaseQueryResult(false, "You already have this user in your contact list!");

                cmd.CommandText = @"INSERT IGNORE INTO friendinvite_notifications (members_senderid,members_receiverid,isread,notificationtext)
                                          SELECT @MemberID,receiverM.memberid,@IsRead,@NotificationText
                                          FROM members receiverM
                                          WHERE receiverM.email=@RecipientUsername";
                result = cmd.ExecuteNonQuery();

                myTrans.Commit();
                return new DatabaseQueryResult(result == 1 ? true : false, result == 1 ? "Friend request sent" : "This member already have a group invitation pending");
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "No users registered with that address");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Accept friend invite. DatabaseQueryResult Value is null.
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="friendUsername"></param>
        /// <returns></returns>
        public DatabaseQueryResult AcceptFriendInvite(int memberID, string friendUsername)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                cmd.Parameters.Add(new MySqlParameter("@FriendUsername", friendUsername));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"INSERT IGNORE INTO friends (members_memberid, members_friendid)
                                          SELECT senderM.memberid,@MemberID
                                          FROM members senderM
                                          WHERE senderM.email=@FriendUsername";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"DELETE FROM friendinvite_notifications 
                                          WHERE members_receiverid=@MemberID
                                          AND members_senderid = (SELECT memberid FROM members WHERE email=@FriendUsername)";
                cmd.ExecuteNonQuery();
                myTrans.Commit();
                return new DatabaseQueryResult(true, string.Empty);
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);

                return new DatabaseQueryResult(false, "An error occured while accepting friend invite");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Accept chat group invite. DatabaseQueryResult Value is null.
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public DatabaseQueryResult AcceptChatGroupInvite(int memberID, int groupID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"INSERT IGNORE INTO membergroups (members_memberid, groups_id)
                                    VALUES(@MemberID,@GroupID)";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"DELETE FROM groupinvite_notifications 
                                          WHERE members_receiverid=@MemberID
                                          AND groups_id = @GroupID";
                cmd.ExecuteNonQuery();
                myTrans.Commit();
                return new DatabaseQueryResult(true, "You are now a member of this group!");
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "An error occured while accepting chat group invite");
            }
            finally
            {
                conn.Close();
            }
        }


        internal void DeclineChatGroupInvite(int memberID, int groupID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"DELETE FROM groupinvite_notifications 
                                          WHERE members_receiverid=@MemberID
                                          AND groups_id = @GroupID";
                cmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        internal void DismissServerInfoNotification(int memberID, int notificationID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Parameters.Add(new MySqlParameter("@NotificationID", notificationID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"DELETE FROM servermessage_notifications 
                                          WHERE members_receiverid=@MemberID
                                          AND servermessages_id = @NotificationID";
                cmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        internal void DeclineFriendInvite(int memberID, string friendUsername)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Parameters.Add(new MySqlParameter("@FriendUsername", friendUsername));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"DELETE FROM friendinvite_notifications 
                                          WHERE members_receiverid=@MemberID
                                          AND members_senderid=(SELECT memberid FROM members WHERE email=@FriendUsername)";
                cmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        internal void StoreAvatar(int memberID, AvatarTransfer avatar)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                string hash = avatar.AvatarByteArray.GetMD5();
                List<MySqlParameter> myParam = new List<MySqlParameter>();
                myParam.Add(new MySqlParameter("@Image", avatar.AvatarByteArray));
                myParam.Add(new MySqlParameter("@Extension", avatar.Extension));
                myParam.Add(new MySqlParameter("@Hash", hash));
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"UPDATE avatars 
                                    SET avatar = @Image, extension=@Extension 
                                    WHERE md5=@Hash";
                cmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        internal void StoreCustomEmoticon(int memberID, ContactEmoticonTransfer emoticon)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                string hash = emoticon.EmoticonByteArray.GetMD5();
                List<MySqlParameter> myParam = new List<MySqlParameter>();
                myParam.Add(new MySqlParameter("@Image", emoticon.EmoticonByteArray));
                myParam.Add(new MySqlParameter("@Extension", emoticon.Extension));
                myParam.Add(new MySqlParameter("@Hash", hash));
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.AddRange(myParam.ToArray());
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"UPDATE emoticons 
                                    SET image = @Image, extension=@Extension 
                                    WHERE md5=@Hash";
                cmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        internal void IgnoreContact(int memberID, string friendUsername)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@FriendUsername", friendUsername));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"insert ignore into ignoredcontacts VALUES(@MemberID,(select memberid from members where email=@FriendUsername))";
                cmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }
        internal void UnignoreContact(int memberID, string friendUsername)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@FriendUsername", friendUsername));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"DELETE FROM ignoredcontacts WHERE members_memberid=@MemberID AND members_friendid=(select memberid from members where email=@FriendUsername)";
                cmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        internal void BlockGroup(int memberID, int groupID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"insert ignore into blockedgroups VALUES(@MemberID,@GroupID)";
                cmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }

        internal void UnblockGroup(int memberID, int groupID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {

                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"DELETE FROM blockedgroups WHERE members_memberid=@MemberID AND groups_id=@GroupID";
                cmd.ExecuteNonQuery();

                myTrans.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Leave a group. DatabaseQueryResult Value is null
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="groupID"></param>
        /// <returns></returns>
        internal DatabaseQueryResult LeaveGroup(int memberID, int groupID)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"DELETE FROM membergroups WHERE members_memberid=@MemberID AND groups_id=@GroupID";
                int result = cmd.ExecuteNonQuery();
                myTrans.Commit();
                if (result == 0)
                    return new DatabaseQueryResult(false, "Leave group failed");
                else
                    return new DatabaseQueryResult(true, "Left group");

            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "Leave group failed");
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Rename a group. DatabaseQueryResult Value is null
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="groupID"></param>
        /// <param name="newGroupName"></param>
        /// <returns></returns>
        internal DatabaseQueryResult RenameGroup(int memberID, int groupID, string newGroupName)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                cmd.Parameters.Add(new MySqlParameter("@MemberID", memberID));
                cmd.Parameters.Add(new MySqlParameter("@GroupName", newGroupName));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"UPDATE groups SET groupname=@GroupName WHERE members_ownerid=@MemberID AND id=@GroupID";
                int result = cmd.ExecuteNonQuery();
                myTrans.Commit();
                if (result == 0)
                {
                    return new DatabaseQueryResult(false, "Group rename failed");
                }
                else
                {
                    return new DatabaseQueryResult(true, "Group renamed successfully");
                }
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "Group rename failed");
            }
            finally
            {
                conn.Close();
            }
        }

        public DatabaseQueryResult RemoveMemberFromGroup(int ownerMemberID, int groupID, string userNameToRemove)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                cmd.Parameters.Add(new MySqlParameter("@OwnerMemberID", ownerMemberID));
                cmd.Parameters.Add(new MySqlParameter("@UserNameToRemove", userNameToRemove));
                cmd.Parameters.Add(new MySqlParameter("@OwnerPrivilege", (int)GroupPrivilege.Owner));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"DELETE m.* FROM membergroups m
                                    INNER JOIN membergroups ownerm ON ownerm.members_memberid=@OwnerMemberID AND ownerm.groups_id=m.groups_id AND ownerm.groupprivileges_id=@OwnerPrivilege
                                    INNER JOIN members mem ON mem.email=@UserNameToRemove
                                    WHERE m.members_memberid = mem.memberid
                                    AND m.groups_id=@GroupID
                                    AND m.groupprivileges_id !=@OwnerPrivilege";
                int result = cmd.ExecuteNonQuery();
                myTrans.Commit();
                if (result == 0)
                {
                    return new DatabaseQueryResult(false, "Removing member from group failed");
                }
                else
                {
                    return new DatabaseQueryResult(true, "Member was removed from the group");
                }
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "Removing member from group failed");
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Register a user. DatabaseQueryResult Value is email validation token.
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public DatabaseQueryResult RegisterUser(string username, string password)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                bool success = false;
                string activation = new ShortGuid(Guid.NewGuid());
                string SaltedPassword = PasswordHash.CreateHash(password);

                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.Parameters.Add(new MySqlParameter("@Password", SaltedPassword));
                cmd.Parameters.Add(new MySqlParameter("@Activation", activation));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;

                cmd.CommandText = @"SELECT memberid
                                          FROM members 
                                          WHERE email=@Username
                                          AND activation IS NOT NULL";
                var obj = cmd.ExecuteScalar();
                if (obj != null)
                {
                    int registeredMemberID = Convert.ToInt32(obj);
                    cmd.Parameters.Add(new MySqlParameter("@RegisteredMemberID", registeredMemberID));
                    cmd.CommandText = @"UPDATE members
                                              SET password=@Password, activation=@Activation
                                              WHERE memberid=@RegisteredMemberID";
                    cmd.ExecuteNonQuery();
                    success = true;
                }
                else
                {
                    cmd.CommandText = @"INSERT IGNORE INTO members (email,password,activation) VALUES(@Username,@Password,@Activation)";
                    int result = cmd.ExecuteNonQuery();
                    if (result == 0)
                        success = false;
                    else
                        success = true;
                }
                myTrans.Commit();

                if (success)
                {
                    return new DatabaseQueryResult(true, "Registration successful", activation);
                }
                else
                {
                    return new DatabaseQueryResult(false, "A member is already registered with that email");
                }
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "User registration failed");
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Stores a password reset request. DatabaseQueryResult Value is email validation token.
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public DatabaseQueryResult StoreResetPasswordRequest(string username)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                // foreach (var client in clients)
                // {
                string validationCode = new ShortGuid(Guid.NewGuid());

                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.Parameters.Add(new MySqlParameter("@Activation", validationCode));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"INSERT INTO reset_password (email,token)
                                    SELECT m.email,@Activation
                                    from members m
                                    WHERE m.email=@Username
                                    ON DUPLICATE KEY
                                    UPDATE token=@Activation";
                int rowsAffected = cmd.ExecuteNonQuery();
                //}
                myTrans.Commit();
                if (rowsAffected == 0)
                    return new DatabaseQueryResult(false, "No user with that username found.", string.Empty);
                else
                    return new DatabaseQueryResult(true, string.Empty, validationCode);
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }

                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "An error occured trying to store password reset request", string.Empty);
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Change a users password with the given validation token and new password. DatabaseQueryResult Value is null.
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="verificationToken"></param>
        /// <param name="newPassword"></param>
        /// <returns></returns>
        public DatabaseQueryResult ChangePassword(string username, string verificationToken, string newPassword)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {
                string activation = new ShortGuid(Guid.NewGuid());
                string SaltedPassword = PasswordHash.CreateHash(newPassword);
                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@Password", SaltedPassword));
                cmd.Parameters.Add(new MySqlParameter("@Activation", verificationToken));
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"UPDATE members m 
                                          INNER JOIN reset_password r ON r.email=m.email AND r.token=@Activation
                                          SET m.password=@Password
                                          WHERE m.email=@Username";
                int rowsAffected = cmd.ExecuteNonQuery();
                myTrans.Commit();
                if (rowsAffected == 0)
                    return new DatabaseQueryResult(false, "No user with that username and verification token found.");
                else
                    return new DatabaseQueryResult(true, "Password changed successfully. You can now log in.");
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }

                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "An error occured trying to update password.");
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// Verify a registration with verification token. DatabaseQueryResult Value is null.
        /// </summary>
        /// <param name="username"></param>
        /// <param name="verificationToken"></param>
        public DatabaseQueryResult VerifyRegistrationMail(string username, string verificationToken)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {

                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@Activation", verificationToken));
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"SELECT memberid
                                          FROM members
                                          WHERE email=@Username
                                          AND activation IS NULL";
                var existingMember = cmd.ExecuteScalar();
                if (existingMember != null)
                    return new DatabaseQueryResult(false, "The account is already activated.");
                cmd.CommandText = @"UPDATE members 
                                          SET activation=NULL
                                          WHERE email=@Username
                                          AND activation=@Activation";
                int rowsAffected = cmd.ExecuteNonQuery();
                myTrans.Commit();
                if (rowsAffected == 0)
                    return new DatabaseQueryResult(false, "No account with that username and verification token found.");
                else
                    return new DatabaseQueryResult(true, "Account created successfully. You can now log in.");
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "An error occured trying to store password reset request");
            }
            finally
            {
                conn.Close();
            }
        }

        public DatabaseQueryResult SetGroupPrivilege(int ownerMemberID, string username, int groupID, GroupPrivilege groupPrivilege)
        {
            MySqlConnection conn = new MySqlConnection(this.ConnStr);
            conn.Open();
            conn.InfoMessage += OnInfoMessage;
            MySqlTransaction myTrans = conn.BeginTransaction();

            try
            {

                MySqlCommand cmd = conn.CreateCommand();
                // Start a local transaction
                cmd.Parameters.Add(new MySqlParameter("@GroupID", groupID));
                cmd.Parameters.Add(new MySqlParameter("@Username", username));
                cmd.Parameters.Add(new MySqlParameter("@OwnerMemberID", ownerMemberID));
                cmd.Parameters.Add(new MySqlParameter("@NewPrivilege", (int)groupPrivilege));
                cmd.Parameters.Add(new MySqlParameter("@OwnerPrivilege", (int)GroupPrivilege.Owner));
                cmd.Connection = conn;
                cmd.Transaction = myTrans;
                cmd.CommandText = @"UPDATE membergroups mg
                                    INNER JOIN membergroups ownermg ON ownermg.members_memberid=@OwnerMemberID AND ownermg.groups_id=mg.groups_id AND ownermg.groupprivileges_id=@OwnerPrivilege
                                    INNER JOIN members m ON m.email=@Username AND m.memberid=mg.members_memberid
                                    SET mg.groupprivileges_id=@NewPrivilege
                                    WHERE mg.groups_id=@GroupID AND @NewPrivilege!=@OwnerPrivilege";
                int rowsAffected = cmd.ExecuteNonQuery();
                myTrans.Commit();
                if (rowsAffected == 0)
                    return new DatabaseQueryResult(false, "Could not change group privilege for the user.");
                else
                    return new DatabaseQueryResult(true, "Privilege changed successfully.");
            }
            catch (Exception ex)
            {
                try
                {
                    myTrans.Rollback();
                }
                catch (SqlException err)
                {
                    if (myTrans.Connection != null)
                    {
                        string errTxt = "An exception of type " + err.GetType() +
                                        " was encountered while attempting to roll back the transaction.";
                        log.Error(errTxt, err);
                    }
                }
                log.Error(string.Format("Error in {0}. Nothing was written: ", System.Reflection.MethodBase.GetCurrentMethod().Name), ex);
                return new DatabaseQueryResult(false, "An error occured trying to change group privilege");
            }
            finally
            {
                conn.Close();
            }
        }
    }
}